#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
insert into intention_dwb.dwb_customer_intention
SELECT
-- fact_dwd_customer_relationship
    dcr.id,
    dcr.create_date_time,
    dcr.update_date_time,
    dcr.deleted,
    dcr.customer_id,
    dcr.origin_type,
    dcr.itcast_school_id,
    dcr.itcast_subject_id,
    dcr.origin_channel,
    dcr.first_customer_clue_id,
    dcr.last_customer_clue_id,
    dcr.process_state,
    dcr.process_time,
    dcr.payment_state,
    dcr.payment_time,
    dcr.signup_state,
    dcr.signup_time,
-- dim_dwd_employee
    de.tdepart_id,
-- dim_dwd_scrm_department
    dsd.name as departmrnt_name ,
--  dim_dwd_itcast_school
    dis.name as itcast_school_name ,
-- dim_dwd_itcast_subject
    dis2.name as itcast_subject_name,
-- dim_dwd_customer_clue
    dcc.session_id,
    dcc.sid,
    dcc.status,
    dcc.seo_source,
    dcc.ip,
    dcc.referrer,
    dcc.clue_state,
-- dim_dwd_customer
    c.area,
-- transform
    if(dcc.origin_type in ('NETSERVICE','PRESIGNUP'), 'online','offline') as O2O,
    case when dcc.clue_state ='VALID_NEW_CLUES' then 'new' when dcc.clue_state ='VALID_PUBLIC_NEW_CLUE' then 'old' else null end as customer_mark,
    SUBSTRING(dcr.create_date_time,1,4) yearinfo,
    SUBSTRING(dcr.create_date_time,6,2) monthinfo,
    SUBSTRING(dcr.create_date_time,9,2) dayinfo,
    SUBSTRING(dcr.create_date_time,12,2) hourinfo,
    '2023-05-06' as dt
FROM (select * from intention_dwd.fact_dwd_customer_relationship where deleted=0) dcr
left join intention_dwd.dim_dwd_employee de on dcr.creator = de.id
left join intention_dwd.dim_dwd_scrm_department dsd on dsd.id = de.tdepart_id
left join intention_dwd.dim_dwd_itcast_school dis on dis.id = dcr.itcast_school_id and itcast_school_id is not null
left join intention_dwd.dim_dwd_itcast_subject dis2 on dis2.id = dcr.itcast_subject_id and itcast_subject_id is not null
left join (select * from intention_dwd.dim_dwd_customer_clue where deleted=0 and valid=0) dcc on dcc.customer_relationship_id = dcr.id
left join (select * from intention_dwd.dim_dwd_customer where deleted=0) c on c.customer_relationship_id = dcr.id;

select * from intention_dwb.dwb_customer_intention;

--有效线索 13136
select count(*) from intention_dwd.dim_dwd_customer_appeal where appeal_status = 1;
----有效线索去重 13129
select count(distinct customer_relationship_first_id) from intention_dwd.dim_dwd_customer_appeal where appeal_status = 1;

--线索个数(含新老) 45918
select count(distinct customer_relationship_id) from intention_dwd.dim_dwd_customer_clue;

insert into intention_dwb.dwb_customer_clue
select
dca.customer_relationship_first_id,
dca.appeal_status,
dcc.create_date_time,
dcc.update_date_time,
dcc.customer_id,
dcc.session_id,
dcc.sid,
dcc.status,
dcc.seo_source,
dcc.area,
dcc.itcast_school_id,
dcc.itcast_school,
dcc.itcast_subject_id,
dcc.itcast_subject,
dcc.origin_type,
dcc.valid,
dcc.clue_state,
dis.name,
sub.name,
if(dcc.origin_type in ('NETSERVICE','PRESIGNUP'), 'online','offline') as O2O,
case when dcc.clue_state ='VALID_NEW_CLUES' then 'new' when dcc.clue_state ='VALID_PUBLIC_NEW_CLUE' then 'old' else null end as customer_mark,
SUBSTRING(dcc.create_date_time,1,4) yearinfo,
SUBSTRING(dcc.create_date_time,6,2) monthinfo,
SUBSTRING(dcc.create_date_time,9,2) dayinfo,
SUBSTRING(dcc.create_date_time,12,2) hourinfo,
'2023-05-06' as dt

from (select * from intention_dwd.dim_dwd_customer_clue where deleted=0 and valid=0) dcc
left join (select * from intention_dwd.dim_dwd_customer_appeal where deleted=0) dca on dca.customer_relationship_first_id=dcc.customer_relationship_id
left join intention_dwd.dim_dwd_itcast_school dis on dis.id = dcc.itcast_school_id and itcast_school_id is not NULL
left join intention_dwd.dim_dwd_itcast_subject sub on sub.id = dcc.itcast_subject_id and itcast_subject_id is not null;

select * from intention_dwb.dwb_customer_clue;
select *
from (select * from intention_dwd.dim_dwd_customer_clue where deleted=0 and valid=0) dcc
left join (select * from intention_dwd.dim_dwd_customer_appeal where deleted=0) dca on dca.customer_relationship_first_id=dcc.customer_relationship_id
where appeal_status is null;

select * from intention_dwd.dim_dwd_customer_clue;
"